/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

suite("query10") {
    String db = context.config.getDbNameByFile(new File(context.file.parent))
    if (isCloudMode()) {
        return
    }
    sql "use ${db}"
    multi_sql """
      set enable_nereids_planner=true;
      set enable_nereids_distribute_planner=false;
      set enable_fallback_to_original_planner=false;
      set exec_mem_limit=21G;
      set be_number_for_test=3;
      set parallel_pipeline_task_num=8;
      set forbid_unknown_col_stats=true;
      set enable_nereids_timeout = false;
      set enable_runtime_filter_prune=false;
      set runtime_filter_type=8;
      set dump_nereids_memo=false;
      set disable_nereids_rules=PRUNE_EMPTY_PARTITION;
      """

    qt_ds_shape_10 '''
    explain shape plan
    select  
      cd_gender,
      cd_marital_status,
      cd_education_status,
      count(*) cnt1,
      cd_purchase_estimate,
      count(*) cnt2,
      cd_credit_rating,
      count(*) cnt3,
      cd_dep_count,
      count(*) cnt4,
      cd_dep_employed_count,
      count(*) cnt5,
      cd_dep_college_count,
      count(*) cnt6
      from
      customer c,customer_address ca,customer_demographics
      where
      c.c_current_addr_sk = ca.ca_address_sk and
      ca_county in ('Fairfield County','Campbell County','Washtenaw County','Escambia County','Cleburne County') and
      cd_demo_sk = c.c_current_cdemo_sk and 
      exists (select *
            from store_sales,date_dim
            where c.c_customer_sk = ss_customer_sk and
                  ss_sold_date_sk = d_date_sk and
                  d_year = 2001 and
                  d_moy between 3 and 3+3) and
      (exists (select *
                  from web_sales,date_dim
                  where c.c_customer_sk = ws_bill_customer_sk and
                        ws_sold_date_sk = d_date_sk and
                        d_year = 2001 and
                        d_moy between 3 ANd 3+3) or 
      exists (select * 
                  from catalog_sales,date_dim
                  where c.c_customer_sk = cs_ship_customer_sk and
                        cs_sold_date_sk = d_date_sk and
                        d_year = 2001 and
                        d_moy between 3 and 3+3))
      group by cd_gender,
            cd_marital_status,
            cd_education_status,
            cd_purchase_estimate,
            cd_credit_rating,
            cd_dep_count,
            cd_dep_employed_count,
            cd_dep_college_count
      order by cd_gender,
            cd_marital_status,
            cd_education_status,
            cd_purchase_estimate,
            cd_credit_rating,
            cd_dep_count,
            cd_dep_employed_count,
            cd_dep_college_count
      limit 100
    '''
}
